﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using RN.ERP.DAL;
using RN.ERP.Entity;
using Fly.Log;
using Newtonsoft.Json;
using RN.ERP.BLL.Utilities;
using RN.ERP.Entity.QD;
using RN.ERP.Entity.ResultModels;
using System.Data.Common;
using System.Data.Entity.Infrastructure;
using System.Data.SqlClient;
using System.Transactions;
using Fly.FlyCommon;
using System.Collections;

namespace RN.ERP.BLL.QD
{
    public class BasicBLL : BaseBasicBLL
    {
        private QD_Context dbContext
        {
            get
            {
                return (QD_Context)base.baseContext;
            }
        }

        public BasicBLL()
        {
            base.baseContext = new QD_Context();
        }

        public override string DoLogin(string userName, string password, string userType, string systemid)
        {
            UserLoginInfo loginInfo = new UserLoginInfo();
            IList<int> roleIdCollection = GetRoleCollection(userType);
            string md5 = MD5Helper.MD5WebEncryptCode(password);
            ////查询用户表信息。
            T_Sys_Users user = dbContext.T_Sys_Users.SingleOrDefault(
                u =>
                u.UserName == userName &&
                (u.Pwd == password || u.Password == md5) &&
                u.bLocked == 0 &&
                u.bDeleted == 0 &&
                roleIdCollection.Contains((int)u.RoleID));
            loginInfo.UserInfo = user;
            if (user != null)
            {
                bool isok = true;
                if (systemid == "2")
                {
                    if (user.RoleID != 105 && user.RoleID != 99105 && user.DogID != 200565 && user.DogID != 8001847)
                    {
                        loginInfo.Message = "您没有权限登录青岛招标代理系统！";
                        loginInfo.Result = false;
                        isok = false;
                    }
                }
                if (isok)
                {
                    loginInfo.Message = "登录成功！";
                    loginInfo.Result = true;
                    ////记录登录日志。
                    UserLogManager.Log(new User_Log()
                    {
                        ObjectId = user.UserGuid.ToString(),
                        Content = "用户登录",
                        AppId = "98",
                        FunctionId = "8020"
                    });
                }
            }
            else
            {
                loginInfo.Message = "用户名或密码错误！";
                loginInfo.Result = false;
            }
            return JsonConvert.SerializeObject(loginInfo);
        }

        public override string DoLogin(string userName, string password, string dogId, string userType, string systemid)
        {
            UserLoginInfo loginInfo = new UserLoginInfo();
            loginInfo.Message = "用户名，密码或加密狗错误！";
            loginInfo.Result = false;
            ////主管用户登录验证
            IList<int> roleIdCollection = GetRoleCollection(userType);
            int dogid = 0;
            int.TryParse(dogId, out dogid);
            string md5 = MD5Helper.MD5WebEncryptCode(password);
            ////查询用户表信息。
            T_Sys_Users user = dbContext.T_Sys_Users.SingleOrDefault(
                u =>
                u.UserName == userName &&
                (u.Pwd == password || u.Password == md5) &&
                u.bLocked == 0 &&
                u.bDeleted == 0 &&
                u.DogID == dogid &&
                roleIdCollection.Contains((int)u.RoleID));
            loginInfo.UserInfo = user;
            if (user != null)
            {
                bool isok = true;
                if (systemid == "2")
                {
                    string adminRole = ConfigurationManager.AppSettings["adminRole"] != null ? ConfigurationManager.AppSettings["adminRole"].ToString() : "";
                    if (!string.IsNullOrEmpty(adminRole))
                    {
                        List<string> adminRoleList = adminRole.Split(',').ToList();
                        if (user.RoleID != 105 && user.RoleID != 99105 && !adminRoleList.Contains(user.DogID.ToString()))
                        {
                            loginInfo.Message = "您没有权限登录青岛招标代理系统！";
                            loginInfo.Result = false;
                            isok = false;
                        }
                    }
                }
                if (isok)
                {
                    loginInfo.Message = "登录成功！";
                    loginInfo.Result = true;
                    ////记录登录日志。
                    UserLogManager.Log(new User_Log()
                    {
                        ObjectId = user.UserGuid.ToString(),
                        Content = "用户登录",
                        AppId = "98",
                        FunctionType = "8020"
                    });
                }
            }
            else
            {
                ////个人用户登录验证
                var empManagers = dbContext.Emp_Manager.Where(
                    e =>
                    e.IDCard == userName &&
                    e.empdogid == dogid &&
                    e.pwd == password &&
                    (e.IsDel != true || e.IsDel == null)
                    ).ToList();
                if (empManagers != null && empManagers.Count > 1)
                {
                    loginInfo.Message = "加密狗已绑定多个账号，无法登录！";
                }
                else if (empManagers != null && empManagers.Count == 1)
                {
                    var empManager = empManagers[0];
                    var sysUser = dbContext.T_Sys_Users.Where(t => t.UserGuid == empManager.CorpGuid).FirstOrDefault();
                    if (sysUser != null)
                    {
                        string TypeFlag = GetTypeFlag(Convert.ToInt32(sysUser.RoleID));
                        if (!string.IsNullOrEmpty(TypeFlag))
                        {
                            loginInfo.EmpManagerInfo = empManager;
                            loginInfo.Message = "登录成功！";
                            loginInfo.Result = true;
                            ////记录登录日志。
                            UserLogManager.Log(new User_Log()
                            {
                                ObjectId = empManager.EmpGuid.ToString(),
                                Content = "个人用户登录",
                                AppId = "98",
                                FunctionId = "8000"
                            });
                        }
                    }
                }
            }
            return JsonConvert.SerializeObject(loginInfo);
        }

        public override string GetUserInfo(string userguid, string areaCode)
        {
            T_Sys_Users user = dbContext.T_Sys_Users.SingleOrDefault(u => u.UserGuid == new Guid(userguid));
            return JsonConvert.SerializeObject(user);
        }

        /// <summary>
        /// 根据Code获取字典表中对应name的值
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public override string GetDictionaryTitleByCode(string code, string type, string areaCode)
        {
            T_Sys_Dictionary dic = dbContext.Dictionarys.Where(d => d.Code == code && d.ParentSID == type && d.IsDel != true).FirstOrDefault();
            if (dic != null)
            {
                return dic.Name;
            }
            return string.Empty;
        }

        public override string GetDictionary(string type, string condition, string areaCode)
        {
            Dictionary<string, string> dicResult = new Dictionary<string, string>();

            if (!string.IsNullOrEmpty(condition) && StringHelper.SqlFilter(condition))
            {
                dicResult.Add("错误", "查询条件包含非法字符!");
            }
            else
            {
                GetDictionaryInternal(type, condition, out dicResult);
            }

            return JsonConvert.SerializeObject(dicResult);
        }

        public override IList<DTO.a_AreaSSQ> BindDdlistArea(DTO.a_AreaSSQ areaInfo, string areaCode)
        {
            IList<a_AreaSSQ> areacode = dbContext.a_AreaSSQ.ToList<a_AreaSSQ>();
            var query = from area in areacode
                        select area;
            if (areaInfo != null)
            {
                if (!string.IsNullOrEmpty(areaInfo.Title))
                {
                    query = query.Where(a => a.Title.Contains(areaInfo.Title));
                }
                if (!string.IsNullOrEmpty(areaInfo.ParentCode))
                {
                    query = query.Where(a => a.ParentCode == areaInfo.ParentCode);
                }
                if (!string.IsNullOrEmpty(areaInfo.Code))
                {
                    query = query.Where(a => a.Code.Contains(areaInfo.Code));
                }
                if (areaInfo.IsOuter != null)
                {
                    query = query.Where(a => a.IsOuter == areaInfo.IsOuter);
                }
                if (areaInfo.ssqFlag != null)
                {
                    query = query.Where(a => a.ssqFlag == areaInfo.ssqFlag);
                }
            }
            //var kv = dbContext.Database.SqlQuery<KeyValue>("select [Title] [value],[Code] [key] from a_AreaSSQ " + strwhere + " ").ToList();
            List<DTO.a_AreaSSQ> list = new List<DTO.a_AreaSSQ>();
            foreach (var item in query)
            {
                DTO.a_AreaSSQ kv_D = new DTO.a_AreaSSQ();
                EntityHelper.ConvertEntity2OtherEntity(item, kv_D);
                list.Add(kv_D);
            }


            return list;
        }

        /// <summary>
        /// 查询字典。
        /// </summary>
        /// <param name="type"></param>
        /// <param name="condition"></param>
        /// <param name="dicResult"></param>
        private void GetDictionaryInternal(string type, string condition, out Dictionary<string, string> dicResult)
        {
            DictionaryDAL dicDal = new DictionaryDAL();
            dicResult = new Dictionary<string, string>();
            IList<KeyValue> listKv = new List<KeyValue>();
            string sql = string.Empty;
            switch (type.ToLower())
            {
                case "area":    ////查询市信息。
                    listKv = dicDal.GetCity(condition);
                    break;
                case "areasub":         ////查询区信息。
                    listKv = dicDal.GetDistrict(condition);
                    break;
                case "areassq":
                    listKv = dicDal.GetAreaSSQ(condition);
                    break;
                case "corptype":        ////查询企业类型。
                    listKv = dicDal.GetCorpType(condition);
                    break;
                case "degree":       ////查询文化程度。
                    listKv = dicDal.GetDegreeType(condition);
                    break;
                case "empdegree":       ////查询文化程度。
                    listKv = dicDal.GetEmpDegreeType(condition);
                    break;
                case "empmajor":        ////查询人员所学专业。
                    listKv = dicDal.GetDic("major");
                    break;
                case "empjobstatus":        ////查询人员在职状态。
                    listKv = dicDal.GetDic("JobAs");
                    break;
                case "corpusertype":        ////查询人员在职状态。
                    listKv = dicDal.GetDic("CorpUserType");
                    break;
                case "zjzy":        ////查询人员在职状态。
                    listKv = dicDal.GetDic("ZJZY");
                    break;
                case "empzctitle":
                    listKv = dicDal.GetDic("EmpZCTitle");
                    break;
                case "wdrqcradetitle":
                    listKv = dicDal.GetDic("wdrqCradeTitle");
                    break;
                case "dutylevel":
                    listKv = dicDal.GetDic("DutyLevel");
                    break;
                case "prodegree":
                    listKv = dicDal.GetDic("ProDegree");
                    break;
                default:
                    dicResult.Add("错误", "未知的字典索引类型！");
                    break;
            }
            try
            {
                foreach (KeyValue kv in listKv)
                {
                    if (!dicResult.Keys.Contains(kv.Key))
                        dicResult.Add(kv.Key, kv.Value);
                }
            }
            catch (Exception ex)
            {
                dicResult.Clear();
                dicResult.Add("错误", ex.Message);
            }
        }

        private IList<int> GetRoleCollection(string userType)
        {
            IList<int> roleIdCollections = new List<int>();
            switch (userType.Trim().ToLower())
            {
                case "manager":
                    roleIdCollections.Add(102);
                    //roleIdCollections.Add(6956);
                    //roleIdCollections.Add(6547);
                    //roleIdCollections.Add(7064);
                    //roleIdCollections.Add(7032);

                    ////安监站
                    if (ConfigurationManager.AppSettings["anjianrole"] != null)
                    {
                        string[] anjianrole = ConfigurationManager.AppSettings["anjianrole"].ToString().Split(',');
                        foreach (string role in anjianrole)
                        {
                            if (!string.IsNullOrEmpty(role))
                            {
                                roleIdCollections.Add(Convert.ToInt32(role));
                            }
                        }
                    }
                    ////质安
                    if (ConfigurationManager.AppSettings["zhianrole"] != null)
                    {
                        string[] zhianrole = ConfigurationManager.AppSettings["zhianrole"].ToString().Split(',');
                        foreach (string role in zhianrole)
                        {
                            if (!string.IsNullOrEmpty(role))
                            {
                                roleIdCollections.Add(Convert.ToInt32(role));
                            }
                        }
                    }

                    ////建管
                    if (ConfigurationManager.AppSettings["jianguanrole"] != null)
                    {
                        string[] jianguanrole = ConfigurationManager.AppSettings["jianguanrole"].ToString().Split(',');
                        foreach (string role in jianguanrole)
                        {
                            if (!string.IsNullOrEmpty(role))
                            {
                                roleIdCollections.Add(Convert.ToInt32(role));
                            }
                        }
                    }
                    break;
                case "corp":
                    roleIdCollections.Add(15);
                    roleIdCollections.Add(17);
                    roleIdCollections.Add(105);
                    roleIdCollections.Add(42);
                    roleIdCollections.Add(46);
                    roleIdCollections.Add(30);
                    roleIdCollections.Add(31);
                    roleIdCollections.Add(45);
                    roleIdCollections.Add(55);
                    roleIdCollections.Add(7330);
                    roleIdCollections.Add(7331);
                    break;
            }
            return roleIdCollections;
        }

        private string GetTypeFlag(int roleId)
        {
            string typeFlag = string.Empty;
            if (roleId == 15 || roleId == 17)
            {
                typeFlag = "0";
            }
            else
                if (roleId == 42)
                {
                    typeFlag = "2";
                }
                else
                    if (roleId == 105 || roleId == 99105)
                    {
                        typeFlag = "-1";
                    }
            return typeFlag;
        }
        #region 变更功能。

        public override string GetChangeItems(string changeType,string appId, string areaCode)
        {
            IList<Tb_ChangeItem> items = null;
            string[] types = changeType.Split(',');
            List<int> itemTypeArray = new List<int>();
            foreach (string str in types)
            {
                if (!string.IsNullOrEmpty(str))
                    itemTypeArray.Add(GetChangeType(str));
            }

            if (itemTypeArray.Count > 0)
            {
                items = dbContext.ChangeItems.Where(c => itemTypeArray.Contains(c.ItemType ?? 0) && c.IsDel != true && c.ClassID == 8020).OrderBy(c => c.SortCode).ToList();
            }
            return JsonConvert.SerializeObject(items);
        }

        /// <summary>
        /// 添加变更申请
        /// </summary>
        /// <param name="changeInfo"></param>
        /// <returns></returns>
        public override string AddChange(
            string changeSetInfo,
            string changeInfo,
            string changeType,
            string userGuid,
            string userRole,
            string userName, string areaCode)
        {
            bool isexist = false;
            int changesetid = 0;
            int itemType = GetChangeType(changeType);
            string sItemType = itemType.ToString();
            OperationResult result = new OperationResult();
            if (!string.IsNullOrEmpty(changeInfo) && !string.IsNullOrEmpty(changeSetInfo))
            {
                ////首先验证针对此变更信息是否未结。
                Tb_ChangeSetRecord changeSet = JsonConvert.DeserializeObject<Tb_ChangeSetRecord>(changeSetInfo);
                var lastChangeSet = from cs in dbContext.ChangeSetInfo
                                    where cs.ObjectType == sItemType && cs.IsDel != true && cs.ObjectId == changeSet.ObjectId
                                    orderby cs.IsrtDate descending
                                    select cs.FlowCode;

                if (lastChangeSet.Count() == 0 || (lastChangeSet.First<string>().StartsWith("1,019,029") || lastChangeSet.First<string>().IndexOf(",021") > 0) && lastChangeSet.First<string>().IndexOf(",022") < 0)
                {
                    DbConnection con = ((IObjectContextAdapter)dbContext).ObjectContext.Connection;
                    con.Open();
                    using (DbTransaction tran = con.BeginTransaction())
                    {
                        try
                        {
                            if (sItemType == "7" || sItemType == "1")
                            {
                                Tb_ChangeSetRecord oldchangeinfo = dbContext.ChangeSetRecords.Where(c => (c.ObjectType == "1" || c.ObjectType == "7") && c.IsDel != true
                                          && c.ObjectId == changeSet.ObjectId && c.IsrtDate == changeSet.IsrtDate).ToList().FirstOrDefault();
                                if (oldchangeinfo != null)
                                {
                                    isexist = true;
                                    changesetid = oldchangeinfo.Id;
                                    Tb_ChangeSetRecord newchange = new Tb_ChangeSetRecord();
                                    newchange.ObjectType = oldchangeinfo.ObjectType + "," + itemType.ToString();
                                    EntityHelper.ConvertEntity2Entity<Tb_ChangeSetRecord>(newchange, oldchangeinfo);
                                    dbContext.SaveChanges();
                                }
                                else
                                {
                                    //1.插入变更集合表
                                    changeSet.ObjectType = itemType.ToString();
                                    changeSet.IsDel = false;
                                    dbContext.ChangeSetRecords.Add(changeSet);
                                    dbContext.SaveChanges();
                                }
                            }
                            else
                            {
                                //1.插入变更集合表
                                changeSet.ObjectType = itemType.ToString();
                                changeSet.IsDel = false;
                                dbContext.ChangeSetRecords.Add(changeSet);
                                dbContext.SaveChanges();
                            }

                            //2.插入变更详细信息表
                            List<Tb_ChangeRecord> recList = JsonConvert.DeserializeObject<List<Tb_ChangeRecord>>(changeInfo);
                            foreach (Tb_ChangeRecord item in recList)
                            {
                                item.ChangeSetId = changeSet.Id;
                                if (isexist)
                                {
                                    item.ChangeSetId = changesetid;
                                }
                                item.OptDate = changeSet.IsrtDate;
                                dbContext.ChangeRecords.Add(item);
                                dbContext.SaveChanges();
                            }

                            ////3.记录审核状态。
                            Tb_FlowCode flowCode = new Tb_FlowCode()
                            {
                                AreaCode = "02",
                                FlowCode = "0,019,",
                                CodeType = 8020,
                                ObjectId = changeSet.Id.ToString(),
                                UpdateTime = DateTime.Now,
                                RoleId = userRole,
                                IsDel = false
                            };
                            dbContext.FlowCodes.Add(flowCode);
                            dbContext.SaveChanges();

                            ////4.插入日志表。
                            UserLogManager.Log(new User_Log()
                            {
                                Content = "添加信息变更",
                                ObjectId = changeSet.Id.ToString(),
                                IsrtUser = userGuid,
                                IsrtUserName = userName,
                                IsrtUserArea = "02",
                                FunctionId = "8020",
                                FunctionType = "1",////企业变更添加
                                AppId = "98"
                            });

                            tran.Commit();

                            ////5.填充返回结果。
                            result.Message = "变更申请添加成功";
                            result.Result = true;
                            result.ReturnValue = changeSet;
                        }
                        catch (Exception ex)
                        {
                            tran.Rollback();
                            //插入日志表
                            SysLogManager.Log(new Sys_Log()
                            {
                                ErrorMessage = ex.Message,
                                LogDate = DateTime.Now
                            });
                            result.Message = "变更添加失败：" + ex.Message;
                            result.Result = false;
                            result.ReturnValue = null;
                        }
                        finally
                        {
                            con.Close();
                        }
                    }
                }
                else
                {
                    result.Message = "存在一条针对此数据的变更记录尚未审核通过，请先处理之前的变更申请！";
                    result.Result = false;
                    result.ReturnValue = null;
                }
            }
            else
            {
                result.Message = "变更信息不完善！";
                result.Result = false;
                result.ReturnValue = null;
            }

            return JsonConvert.SerializeObject(result);
        }

        /// <summary>
        /// 提交变更申请集合
        /// </summary>
        /// <param name="changeIds"></param>
        /// <returns></returns>
        public override string SubmitChange(
            string changeIds,
            string changeType,
            string userGuid,
            string userRole,
            string userName, string areaCode)
        {
            OperationResult result = new OperationResult();
            int itemType = GetChangeType(changeType);
            IList<string> changeids = JsonConvert.DeserializeObject<IList<string>>(changeIds);
            if (changeids.Count == 0)
            {
                result.Message = "请至少选择一条变更项!";
                result.Result = false;
            }
            else
            {
                DbConnection con = ((IObjectContextAdapter)dbContext).ObjectContext.Connection;
                con.Open();
                using (DbTransaction tran = con.BeginTransaction())
                {
                    try
                    {

                        foreach (string changeId in changeids)
                        {
                            int id = 0;
                            int.TryParse(changeId, out id);
                            string sItemType = itemType.ToString();
                            V_ChangeInfo changesetInfo = dbContext.ChangeSetInfo.SingleOrDefault(cs => cs.ChangeSetId == id && cs.ObjectType == sItemType && cs.IsDel != true);
                            if (changesetInfo != null)
                            {
                                string contentMes = string.Empty;
                                if (changesetInfo.FlowCode.StartsWith("1"))
                                {
                                    result.Message = "变更已审核通过，不能上报！";
                                    contentMes = result.Message;
                                    result.Result = false;
                                }
                                else if (changesetInfo.FlowCode.StartsWith("0,019") && !changesetInfo.FlowCode.Contains(",021"))
                                {
                                    result.Message = "变更已上报！";
                                    contentMes = result.Message;
                                    result.Result = false;
                                }
                                else
                                {
                                    ////1.更新状态。
                                    if (changesetInfo.FlowCode.IndexOf(",021") > 0)
                                    {
                                        changesetInfo.FlowCode = "0,019,022,";
                                        contentMes = "再次上报变更信息";
                                    }
                                    else
                                    {
                                        changesetInfo.FlowCode = "0,019,";
                                        contentMes = "上报信息变更";
                                    }

                                    dbContext.SaveChanges();
                                    ////3.填充返回结果。
                                    result.Message = "变更申请上报成功";
                                    result.Result = true;
                                }
                                ////2.插入日志表。
                                UserLogManager.Log(new User_Log()
                                {
                                    Content = contentMes,
                                    ObjectId = changeId,
                                    IsrtUser = userGuid,
                                    IsrtUserName = userName,
                                    IsrtUserArea = "02",
                                    FunctionType = "3",////变更上报
                                    FunctionId = "8020",
                                    AppId = "98"
                                });
                            }
                        }
                        tran.Commit();


                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        //插入日志表
                        SysLogManager.Log(new Sys_Log()
                        {
                            ErrorMessage = ex.Message,
                            LogDate = DateTime.Now
                        });
                        result.Message = "变更上报失败：" + ex.Message;
                        result.Result = false;
                        result.ReturnValue = null;
                    }
                    finally
                    {
                        con.Close();
                    }
                }
            }
            return JsonConvert.SerializeObject(result);
        }

        public override string EditChange(
            string changeSetInfo,
            string changeInfo,
            string changeType,
            string userGuid,
            string userRole,
            string userName, string areaCode)
        {
            OperationResult result = new OperationResult();
            int itemType = GetChangeType(changeType);
            string sItemType = itemType.ToString();
            if (!string.IsNullOrEmpty(changeInfo) && !string.IsNullOrEmpty(changeSetInfo))
            {
                Tb_ChangeSetRecord changeSet = JsonConvert.DeserializeObject<Tb_ChangeSetRecord>(changeSetInfo);
                var lastChangeSet = from cs in dbContext.ChangeSetInfo
                                    where cs.ObjectType == sItemType && cs.IsDel != true && cs.ChangeSetId == changeSet.Id
                                    orderby cs.IsrtDate descending
                                    select cs.FlowCode;
                Tb_ChangeSetRecord changeSet1 = dbContext.ChangeSetRecords.SingleOrDefault(c => c.Id == changeSet.Id);
                if (changeSet1 == null)
                {
                    result.Message = "没有此项变更信息！";
                    result.Result = false;
                    result.ReturnValue = null;
                }
                else if (lastChangeSet.Count() == 0 || lastChangeSet.First() == "0" || lastChangeSet.First().StartsWith("0,019,021"))
                {
                    DbConnection con = ((IObjectContextAdapter)dbContext).ObjectContext.Connection;
                    con.Open();
                    using (DbTransaction tran = con.BeginTransaction())
                    {
                        try
                        {
                            DateTime optDate = DateTime.Now;
                            ////1.删除变更子项信息。
                            ChangeDAL changeDal = new ChangeDAL(dbContext);
                            changeDal.DeleteChanges(changeSet.Id);
                            dbContext.SaveChanges();

                            ////2.更新变更主表。
                            changeSet1.Description = changeSet.Description;
                            //changeSet1.IsrtDate = optDate;
                            changeSet1.ObjectType = sItemType;
                            changeSet1.ObjectName = changeSet.ObjectName;
                            dbContext.SaveChanges();

                            ////3.添加子表。
                            List<Tb_ChangeRecord> recList = JsonConvert.DeserializeObject<List<Tb_ChangeRecord>>(changeInfo);
                            foreach (Tb_ChangeRecord item in recList)
                            {
                                item.ChangeSetId = changeSet.Id;
                                item.OptDate = optDate;
                                dbContext.ChangeRecords.Add(item);
                                dbContext.SaveChanges();
                            }

                            ////4.修改审核状态为 再次上报。
                            string changeSetID = changeSet.Id.ToString();
                            Tb_FlowCode flowCode = dbContext.FlowCodes.Where(m => m.ObjectId == changeSetID && m.CodeType == 8020 && m.IsDel != true).FirstOrDefault();
                            flowCode.FlowCode = "0,019,022,";
                            dbContext.SaveChanges();

                            ////5.插入日志表。
                            UserLogManager.Log(new User_Log()
                            {
                                Content = "修改信息变更",
                                ObjectId = changeSet.Id.ToString(),
                                IsrtUser = userGuid,
                                IsrtUserName = userName,
                                IsrtUserArea = "02",
                                FunctionType = "2",////变更修改
                                FunctionId = "8020",
                                AppId = "98"
                            });

                            tran.Commit();

                            ////6.填充返回结果。
                            result.Message = "变更申请修改成功";
                            result.Result = true;
                            result.ReturnValue = changeSet;
                        }
                        catch (Exception ex)
                        {
                            tran.Rollback();
                            //插入日志表
                            SysLogManager.Log(new Sys_Log()
                            {
                                ErrorMessage = ex.Message,
                                LogDate = DateTime.Now
                            });
                            result.Message = "变更添加失败：" + ex.Message;
                            result.Result = false;
                            result.ReturnValue = null;
                        }
                        finally
                        {
                            con.Close();
                        }
                    }
                }
                else
                {
                    result.Message = "此项信息正在审核中，不允许修改！";
                    result.Result = false;
                    result.ReturnValue = null;
                }
            }
            else
            {
                result.Message = "变更信息不完善！";
                result.Result = false;
                result.ReturnValue = null;
            }

            return JsonConvert.SerializeObject(result);
        }

        public override string DeleteChange(
            string changeId,
            string changeType,
            string userGuid,
            string userRole,
            string userName, string areaCode)
        {
            OperationResult result = new OperationResult();

            int changeid = 0;
            int.TryParse(changeId, out changeid);
            int itemType = GetChangeType(changeType);
            string sItemType = itemType.ToString();
            var lastChangeSet = from cs in dbContext.ChangeSetInfo
                                where cs.ObjectType == sItemType && cs.IsDel != true && cs.ChangeSetId == changeid
                                orderby cs.IsrtDate descending
                                select cs.FlowCode;
            Tb_ChangeSetRecord changeSet = dbContext.ChangeSetRecords.SingleOrDefault(c => c.Id == changeid && c.IsDel != true && c.ObjectType == sItemType);
            if (changeSet == null)
            {
                result.Message = "没有此项变更！";
                result.Result = false;
                result.ReturnValue = null;
            }
            else if (lastChangeSet.Count() == 0 || lastChangeSet.First() == "0" || lastChangeSet.First().StartsWith("0,019,021"))
            {
                changeSet.IsDel = true;
                dbContext.SaveChanges();
                result.Message = "删除变更成功！";
                result.Result = true;
                result.ReturnValue = null;
            }
            else if (lastChangeSet.First().StartsWith("1"))
            {
                result.Message = "此项信息已通过审核，不允许删除！";
                result.Result = false;
                result.ReturnValue = null;
            }
            else
            {
                result.Message = "此项信息正在审核中，不允许删除！";
                result.Result = false;
                result.ReturnValue = null;
            }

            return JsonConvert.SerializeObject(result);
        }

        public override string GetBeforeChangeValue(string changeItem, string areaCode, params string[] parameters)
        {
            if (StringHelper.SqlFilter(parameters))
            {
                return "查询条件包含非法字符!";
            }
            Tb_ChangeItem cItem = dbContext.ChangeItems.SingleOrDefault(c => c.ChangeItem == changeItem && c.IsDel != true && c.ClassID == 8020);
            if (cItem != null)
            {
                string strSql = string.Format(cItem.BeforeSQL, parameters);
                List<KeyValue> result = dbContext.Database.SqlQuery<KeyValue>(strSql).ToList();
                if (result.Count > 0)
                {
                    Dictionary<string, string> dicValue = new Dictionary<string, string>();
                    dicValue.Add(result[0].Key ?? string.Empty, result[0].Value ?? string.Empty);
                    return JsonConvert.SerializeObject(dicValue);
                }
                else
                {
                    return string.Empty;
                }
            }
            else
            {
                return "未知的字典索引类型!";
            }
        }

        public override string GetBindChangeValue(string changeItem, string areaCode, params string[] parameters)
        {
            if (StringHelper.SqlFilter(parameters))
            {
                return "查询条件包含非法字符!";
            }
            Tb_ChangeItem cItem = dbContext.ChangeItems.SingleOrDefault(c => c.ChangeItem == changeItem && c.IsDel != true && c.ClassID == 8020);
            if (cItem != null)
            {
                string strSql = string.Empty;
                if (parameters == null || parameters.Length == 0)
                {
                    strSql = cItem.BindSQL;
                }
                else if (cItem.BindSQL != null)
                {
                    strSql = string.Format(cItem.BindSQL, parameters);
                }
                List<KeyValue> result = dbContext.Database.SqlQuery<KeyValue>(strSql).ToList();
                if (result.Count > 0)
                {
                    Dictionary<string, string> dicValue = new Dictionary<string, string>();
                    foreach (KeyValue kv in result)
                    {
                        if (kv.Key != null)
                        {
                            if (dicValue.Keys.Contains(kv.Key))
                            {
                                continue;
                            }
                            dicValue.Add(kv.Key, kv.Value);
                        }
                    }
                    return JsonConvert.SerializeObject(dicValue);
                }
                else
                {
                    return string.Empty;
                }
            }
            else
            {
                return string.Empty;
            }
        }

        public override string GetChanges(string changeType, string corpUserGuid, string flowCode, string condition, int pageIndex, int pageSize, out int count, string areaCode)
        {
            count = 0;
            if (!string.IsNullOrEmpty(condition))
            {
                if (StringHelper.SqlFilter(condition))
                {
                    return "查询条件包含非法字符!";
                }
            }

            ChangeDAL changeDal = new ChangeDAL(this.dbContext);
            IList<V_ChangeInfo> changeInfos = changeDal.GetChanges(GetChangeType(changeType).ToString(), corpUserGuid, flowCode, condition, pageIndex, pageSize, out count);
            return JsonConvert.SerializeObject(changeInfos);
        }

        //public override int GetChangesCount(string changeType, string corpUserGuid, string flowCode, string condition, int pageIndex, int pageSize)
        //{
        //    if (!string.IsNullOrEmpty(condition))
        //    {
        //        if (StringHelper.SqlFilter(condition))
        //        {
        //            return 0;
        //        }
        //    }

        //    ChangeDAL changeDal = new ChangeDAL(this.dbContext);
        //    int count = changeDal.GetChangesCount(GetChangeType(changeType).ToString(), corpUserGuid, flowCode, condition, pageIndex, pageSize);
        //    return count;
        //}

        public override string GetChangeDetails(string changeType, int changeSetId, string areaCode)
        {
            int itemType = GetChangeType(changeType);
            string sItemType = itemType.ToString();
            Tb_ChangeSetRecord changeSetInfo;
            if (itemType == 1)
            {
                changeSetInfo = dbContext.ChangeSetRecords.SingleOrDefault(cs => cs.Id == changeSetId && (cs.ObjectType == "1" || cs.ObjectType == "3" || cs.ObjectType == "7" || cs.ObjectType == "1,7" || cs.ObjectType == "7,1") && cs.IsDel != true);
            }
            else if (itemType == 4)
            {
                changeSetInfo = dbContext.ChangeSetRecords.SingleOrDefault(cs => cs.Id == changeSetId && (cs.ObjectType == "4" || cs.ObjectType == "6") && cs.IsDel != true);
            }
            else if (itemType == 8)
            {
                changeSetInfo = dbContext.ChangeSetRecords.SingleOrDefault(cs => cs.Id == changeSetId && (cs.ObjectType == "1,7" || cs.ObjectType == "7,1") && cs.IsDel != true);
            }
            else
            {
                changeSetInfo = dbContext.ChangeSetRecords.SingleOrDefault(cs => cs.Id == changeSetId && cs.ObjectType == sItemType && cs.IsDel != true);
            }

            if (changeSetInfo == null)
            {
                return "";
            }
            IList<Tb_ChangeRecord> changeInfos = dbContext.ChangeRecords.Where(c => c.ChangeSetId == changeSetId).ToList();
            ChangeDetail changeDetail = new ChangeDetail();
            changeDetail.ChangeSet = changeSetInfo;
            changeDetail.ChangeInfos = changeInfos;
            return JsonConvert.SerializeObject(changeDetail);
        }

        private int GetChangeType(string changeType)
        {
            int itemType = 0;
            switch (changeType.ToLower())
            {
                case "corp":
                    itemType = 1;
                    break;
                case "safe":
                    itemType = 3;
                    break;
                case "corpquality":
                    itemType = 2;
                    break;
                case "person":
                    itemType = 4;
                    break;
                case "personquality":
                    itemType = 5;
                    break;
                case "personqualityb":
                    itemType = 6;
                    break;
                case "corpwdrq":
                    itemType = 7;
                    break;
                case "corpall":
                    itemType = 8;
                    break;


            }
            return itemType;
        }

        public string GetClassValue(string changeItem, params string[] parameters)
        {
            if (StringHelper.SqlFilter(parameters))
            {
                return "查询条件包含非法字符!";
            }
            Tb_ChangeItem cItem = dbContext.ChangeItems.SingleOrDefault(c => c.ChangeItem == changeItem && c.IsDel != true && c.ClassID == 8020);
            if (cItem != null)
            {
                string strSql = string.Empty;
                if (parameters == null || parameters.Length == 0)
                {
                    strSql = cItem.BindSQL;
                }
                else
                {
                    strSql = string.Format(cItem.BindSQL, parameters);
                }
                List<KeyValue> result = dbContext.Database.SqlQuery<KeyValue>(strSql).ToList();
                if (result.Count > 0)
                {
                    Dictionary<string, string> dicValue = new Dictionary<string, string>();
                    foreach (KeyValue kv in result)
                    {
                        if (dicValue.Keys.Contains(kv.Key))
                        {
                            continue;
                        }
                        dicValue.Add(kv.Key, kv.Value);
                    }
                    return JsonConvert.SerializeObject(dicValue);
                }
                else
                {
                    return string.Empty;
                }
            }
            else
            {
                return string.Empty;
            }
        }

        #endregion

        #region 变更审核
        public override string AuditChange(string changeId, string flowCode, string description, bool isFinal, string changeType, string userGuid, string userRole, string userName, string areaCode)
        {
            OperationResult result = new OperationResult();
            bool rst = true;
            try
            {
                if (flowCode != null && flowCode.StartsWith("1,019,029"))  //审核通过  //未加事务处理
                {
                    rst = UpdateTables(changeId, flowCode);  ////审核通过更新数据
                    if (!rst)
                    {
                        result.Message = "审核不通过,";
                    }
                    else
                    {
                        result.Message = "审核通过,";
                    }
                }
                else
                {
                    this.UpdateFlowCode(changeId, flowCode, description);////审核未通过修改状态
                    result.Message = "退回申请,";
                }
                result.Result = rst;
                result.Message += rst ? "操作成功" : "操作失败";

                ////4.插入日志表。
                UserLogManager.Log(new User_Log()
                {
                    Content = (changeType.ToLower().IndexOf("person") > 0 ? ("主管审核人员变更") : ("主管审核企业变更")) + ":" + result.Message,
                    ObjectId = changeId,
                    IsrtUser = userGuid,
                    IsrtUserName = userName,
                    IsrtUserArea = "02",
                    FunctionType = flowCode.StartsWith("1") ? ("4") : ("5"),////4.审核通过  5.主管退回
                    FunctionId = "8020",
                    AppId = "98"
                });
            }
            catch (Exception ex)
            {
                result.Result = false;
                result.Message = "变更审核程序出错";
            }
            return JsonConvert.SerializeObject(result);
        }
        /// <summary>
        /// 审核通过更新表数据
        /// </summary>
        /// <param name="changeId"></param>
        private bool UpdateTables(string changeId, string flowCode)
        {
            ////1、获取变更集及关联数据
            var changeList = new ChangeDAL(dbContext).GetChangeItemRecordInfo(int.Parse(changeId));
            if (changeList != null && changeList.Count > 0)
            {

                Dictionary<string, string> tables = GetUpdateTables(changeList);
                if (tables != null && tables.Count > 0)
                {
                    string objectID = changeList[0].ObjectId;
                    if (string.IsNullOrEmpty(objectID))
                        return false;
                    DbConnection conn = ((IObjectContextAdapter)dbContext).ObjectContext.Connection;
                    conn.Open();
                    using (DbTransaction tran = conn.BeginTransaction())
                    {
                        try
                        {
                            #region 更新数据
                            var SqlCommands = new List<SqlCommandParameter>();

                            foreach (string table in tables.Keys)
                            {
                                List<SqlParameter> parameters = new List<SqlParameter>() { new SqlParameter("@ObjectId", objectID) };

                                string sql = string.Format("update {0} set ", table);
                                foreach (ChangeItemRecordInfo change in changeList)
                                {
                                    if (change.UpdateTable.Split('|')[0] == table)
                                    {
                                        if (change.UpdateColumn.IndexOf('|') > 0 && (!string.IsNullOrEmpty(change.UpdateColumn.Split('|')[1])))  //处理类似”级别“这用name：value的更新
                                        {
                                            //对每一类对应表中的记录，拼接sql更新
                                            sql += string.Format("{0}=@{1},", change.UpdateColumn.Split('|')[0], change.UpdateColumn.Split('|')[0]);
                                            parameters.Add(new SqlParameter(string.Format("@{0}", change.UpdateColumn.Split('|')[0]), change.AfterChange));
                                            sql += string.Format("{0}=@{1},", change.UpdateColumn.Split('|')[1], change.UpdateColumn.Split('|')[1]);
                                            parameters.Add(new SqlParameter(string.Format("@{0}", change.UpdateColumn.Split('|')[1]), change.AfterValue));
                                        }
                                        else
                                        {
                                            switch (change.UpdateColumn.ToLower())
                                            {
                                                case "certificatename":
                                                    string sqlExt = string.Format("update tb_emp_Manager set TypeFlag=@{0} WHERE EmpGuid=(SELECT TOP 1 EmpGuid FROM dbo.tb_Emp_TitleDetails WHERE ID=@{1})", "TypeFlag", "ObjectId");
                                                    List<SqlParameter> cer_parameters = new List<SqlParameter>() { new SqlParameter("@ObjectId", objectID) };
                                                    cer_parameters.Add(new SqlParameter(string.Format("@{0}", "TypeFlag"), change.AfterValue));
                                                    SqlCommands.Add(new SqlCommandParameter() { Sql = sqlExt, Parameters = cer_parameters });
                                                    break;
                                                case "birthday":
                                                    #region 处理出生年月
                                                    string year;
                                                    string month;
                                                    ParseBirthdayYearMonth(change.AfterValue, out year, out month);
                                                    #endregion
                                                    sql += string.Format("{0}=@{1},", "BithdayYear", "BithdayYear");
                                                    parameters.Add(new SqlParameter(string.Format("@{0}", "BithdayYear"), year));
                                                    sql += string.Format("{0}=@{1},", "BithdayMonth", "BithdayMonth");
                                                    parameters.Add(new SqlParameter(string.Format("@{0}", "BithdayMonth"), month));
                                                    break;
                                                case "biso":
                                                    if (!string.IsNullOrEmpty(change.AfterValue))
                                                    {
                                                        string[] bisolist = change.AfterValue.Split(',');
                                                        sql += string.Format("{0}=@{1},", "bISO14000", "bISO14000");
                                                        parameters.Add(new SqlParameter(string.Format("@{0}", "bISO14000"), bisolist[0]));
                                                        sql += string.Format("{0}=@{1},", "bISO9000", "bISO9000");
                                                        parameters.Add(new SqlParameter(string.Format("@{0}", "bISO9000"), bisolist[1]));
                                                        sql += string.Format("{0}=@{1},", "bISO9001", "bISO9001");
                                                        parameters.Add(new SqlParameter(string.Format("@{0}", "bISO9001"), bisolist[2]));
                                                        sql += string.Format("{0}=@{1},", "bOHSMS18000", "bOHSMS18000");
                                                        parameters.Add(new SqlParameter(string.Format("@{0}", "bOHSMS18000"), bisolist[3]));
                                                    }
                                                    break;
                                            }
                                            //对每一类对应表中的记录，拼接sql更新
                                            if (change.UpdateColumn.ToLower() != "biso")
                                            {
                                                sql += string.Format("{0}=@{1},", change.UpdateColumn, change.ChangeItem);
                                                parameters.Add(new SqlParameter(string.Format("@{0}", change.ChangeItem), (change.SelectType != null && change.SelectType.ToLower() == "t") ? change.AfterChange : change.AfterValue));
                                            }
                                        }
                                    }
                                }
                                sql = sql.TrimEnd(',');
                                sql += tables[table];

                                ///执行sql
                                dbContext.Database.ExecuteSqlCommand(sql, parameters.ToArray());
                                SqlCommands.ForEach(r => dbContext.Database.ExecuteSqlCommand(r.Sql, r.Parameters.ToArray()));
                                //if (SqlCommands != null && SqlCommands.Count > 0)
                                //{
                                //    foreach (var sqlcom in SqlCommands)
                                //    {
                                //        dbContext.Database.ExecuteSqlCommand(sqlcom.Sql, sqlcom.Parameters.ToArray());
                                //    }
                                //}

                            }
                            #endregion
                            this.UpdateFlowCode(changeId, flowCode, "");///修改状态
                            tran.Commit();
                            ///写日志
                        }
                        catch (Exception ex)
                        {
                            tran.Rollback();
                            //插入日志表
                            SysLogManager.Log(new Sys_Log()
                            {
                                ErrorMessage = string.Format("变更集ID{0}：错误信息{1}", changeId, ex.Message),
                                LogDate = DateTime.Now
                            });
                            return false;
                        }
                        finally
                        {
                            conn.Close();
                        }

                    }
                }
            }
            return true;
        }

        private static void ParseBirthdayYearMonth(string birValue, out string year, out string month)
        {
            year = "0";
            month = "0";

            bool flag = false;

            try
            {
                DateTime bir = DateTime.Now;
                DateTime.TryParse(birValue, out bir);
                year = bir.Year.ToString();
                month = bir.Month.ToString();
            }
            catch
            { flag = true; }
            if (flag)
            {
                try
                {
                    birValue = birValue.Replace('/', '-');
                    year = birValue.Split('-')[0];
                    month = birValue.Split('-')[1];
                }
                catch (Exception ex)
                {
                }

            }
        }

        /// <summary>
        /// 更改审核状态
        /// </summary>
        /// <param name="changeId"></param>
        /// <param name="flowCode"></param>
        /// <returns></returns>
        private bool UpdateFlowCode(string changeId, string flowCode, string description)
        {

            try
            {
                Tb_FlowCode flow = dbContext.FlowCodes.SingleOrDefault(r => r.ObjectId == changeId && r.CodeType == 8020 && r.IsDel != true);
                if (flow != null)
                {
                    ////1.更新状态。
                    flow.FlowCode = flowCode;
                    flow.Description = description;
                    dbContext.SaveChanges();
                    return true;
                }
            }
            catch (Exception ex)
            { //插入日志表
                SysLogManager.Log(new Sys_Log()
                {
                    ErrorMessage = ex.Message,
                    LogDate = DateTime.Now
                });
            }
            return false;
        }
        /// <summary>
        /// 提取影响的表名及where条件  一次变更集针对同一张表中的where是一致的，取第一次条件即可    
        /// </summary>
        /// <param name="changeList"></param>
        /// <returns></returns>
        private Dictionary<string, string> GetUpdateTables(List<ChangeItemRecordInfo> changeList)
        {
            Dictionary<string, string> tableDict = new Dictionary<string, string>();///更改表字典 key:表名，value:where条件
            foreach (ChangeItemRecordInfo record in changeList)
            {
                string table = record.UpdateTable;

                if (table != null)
                {
                    string[] tableWhere = table.Split('|');
                    string tableName = table.Split('|')[0];
                    if (!tableDict.Keys.Contains(tableName))
                    {
                        if (tableWhere.Length > 2)  ///为安全期间，如果UpdateTable列中"|"个数少于2则不处理该更改
                        {
                            string where = string.Format(" where {0}=@ObjectId {1}", tableWhere[1], tableWhere[2]);
                            tableDict.Add(tableName, where);
                        }

                    }
                }
            }
            return tableDict;
        }

        public override string AuditLogs(string objectId, string areaCode)
        {
            return JsonConvert.SerializeObject(new ChangeDAL(dbContext).AuditLogs(objectId));
        }
        #endregion

        public override IList<DTO.DictQualityInfo> GetQualitiesDic(string rootCode, string parentId)
        {
            int parentid = 0;
            int.TryParse(parentId, out parentid);
            var dbDict = dbContext.Dictionary;
            var query = from dict in dbDict
                        where dict.IsDel == false && dict.ParentSID == parentid && dict.RootCode == rootCode
                        orderby dict.SortValue ascending
                        select new DTO.DictQualityInfo()
                        {
                            Id = dict.SID,
                            Code = dict.Code,
                            Title = dict.Name
                        };

            var result = query.ToList();
            if (result.Count == 0)
            {
                var dbDictRelation = dbContext.DictionaryRelation;
                var query1 = from dictRel in dbDictRelation
                             where dictRel.ParentID == parentid
                             select dictRel.ChildID ?? 0;
                IList<int> childIds = query1.ToList();
                query = from dict in dbDict
                        where dict.IsDel == false && childIds.Contains(dict.SID)
                        orderby dict.SortValue ascending
                        select new DTO.DictQualityInfo()
                        {
                            Id = dict.SID,
                            Code = dict.Code,
                            Title = dict.Name
                        };
                result = query.ToList();
            }
            return result;
        }

        #region 地区控件调用
        /// <summary>
        /// 获取市级
        /// </summary>
        /// <returns></returns>
        public override List<DTO.a_AreaInfo> GetCity()
        {
            var query = from areainfo in dbContext.AreaInfo
                        where areainfo.Code != "00"
                        orderby areainfo.sortIndex
                        select areainfo;
            List<a_AreaInfo> list = query.ToList();
            List<DTO.a_AreaInfo> lists = new List<DTO.a_AreaInfo>();
            foreach (a_AreaInfo item in list)
            {
                DTO.a_AreaInfo area = new DTO.a_AreaInfo();
                EntityHelper.ConvertEntity2OtherEntity(item, area);
                lists.Add(area);
            }
            return lists;
        }

        /// <summary>
        /// 获取区级
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public override List<DTO.a_AreaSubInfo> GetQu(string code)
        {
            var query = dbContext.AreaSubInfo.Where(m => m.Code == code).OrderBy(m => m.SortCode);
            List<a_AreaSubInfo> list = query.ToList();
            List<DTO.a_AreaSubInfo> lists = new List<DTO.a_AreaSubInfo>();
            foreach (a_AreaSubInfo item in list)
            {
                DTO.a_AreaSubInfo area = new DTO.a_AreaSubInfo();
                EntityHelper.ConvertEntity2OtherEntity(item, area);
                lists.Add(area);
            }
            return lists;
        }

        /// <summary>
        /// 根据区级code获得地址
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public override string GetAddress(string code)
        {
            var query1 = from areasub in dbContext.AreaSubInfo
                         join areainfo in dbContext.AreaInfo on areasub.Code equals areainfo.Code
                         where areasub.SortCode == code
                         select areainfo.Title + areasub.Title;
            return query1.FirstOrDefault();
        }
        # endregion


        /// <summary>
        /// 重置密码
        /// </summary>
        /// <param name="userGuid"></param>
        /// <param name="newPwd"></param>
        /// <param name="areaCode"></param>
        /// <returns></returns>
        public override bool ResetPassword(Guid userGuid, string newPwd, string areaCode)
        {
            bool rst = false;
            var user = dbContext.T_Sys_Users.Where(r => r.UserGuid == userGuid).FirstOrDefault();
            if (user != null)
            {
                try
                {
                    user.Pwd = newPwd;
                    dbContext.SaveChanges();
                    rst = true;
                }
                catch (Exception ex)
                {

                }
            }

            return rst;
        }

    }
}
